const fs = require('fs');
const path = require('path');
const initSqlJs = require('sql.js');

// 数据库文件路径
const dbPath = path.join(__dirname, '../server/warehouse.db');

async function verifyAuditSetup() {
  try {
    const SQL = await initSqlJs();
    
    // 检查数据库文件是否存在
    if (!fs.existsSync(dbPath)) {
      console.log('❌ 数据库文件不存在:', dbPath);
      return;
    }
    
    // 从文件加载数据库
    const fileBuffer = fs.readFileSync(dbPath);
    const db = new SQL.Database(fileBuffer);
    console.log('✅ 数据库已加载');
    
    // 验证审计表是否存在
    console.log('\n🔍 验证审计表...');
    try {
      const tableStmt = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='tasklist_audit'");
      const tableResult = tableStmt.getAsObject();
      if (tableResult) {
        console.log('✅ 审计表存在');
        
        // 显示表结构
        const schemaStmt = db.prepare("PRAGMA table_info(tasklist_audit)");
        console.log('📋 审计表结构:');
        while (schemaStmt.step()) {
          const row = schemaStmt.getAsObject();
          console.log(`  - ${row.name} (${row.type}) ${row.notnull ? 'NOT NULL' : ''} ${row.pk ? 'PRIMARY KEY' : ''}`);
        }
      } else {
        console.log('❌ 审计表不存在');
      }
    } catch (error) {
      console.error('❌ 审计表验证失败:', error.message);
    }
    
    // 验证触发器是否存在
    console.log('\n🔍 验证触发器...');
    try {
      const triggerStmt = db.prepare("SELECT name FROM sqlite_master WHERE type='trigger' AND name LIKE 'track_tasklist_%'");
      const triggers = [];
      while (triggerStmt.step()) {
        triggers.push(triggerStmt.getAsObject());
      }
      
      if (triggers.length > 0) {
        console.log(`✅ 找到 ${triggers.length} 个触发器:`);
        triggers.forEach(trigger => {
          console.log(`  - ${trigger.name}`);
        });
      } else {
        console.log('❌ 未找到触发器');
      }
    } catch (error) {
      console.error('❌ 触发器验证失败:', error.message);
    }
    
    // 测试插入数据并检查审计记录
    console.log('\n🧪 测试审计功能...');
    try {
      // 开始事务
      db.run("BEGIN TRANSACTION");
      
      // 插入测试任务
      const insertSQL = `
        INSERT INTO TaskList (
          id, Task_No, Job_Style, Unload_Line, Unload_Col, Unload_Layer, is_issued, created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
      `;
      
      const taskId = 'test-' + Date.now();
      const params = [
        taskId,
        'TEST-001',
        '测试任务',
        1,
        1,
        1,
        0,
        new Date().toISOString(),
        new Date().toISOString()
      ];
      
      db.run(insertSQL, params);
      console.log('✅ 测试任务插入成功');
      
      // 更新任务状态
      const updateSQL = "UPDATE TaskList SET is_issued = ?, updated_at = ? WHERE id = ?";
      db.run(updateSQL, [1, new Date().toISOString(), taskId]);
      console.log('✅ 任务状态更新成功');
      
      // 查询审计记录
      const auditStmt = db.prepare("SELECT * FROM tasklist_audit WHERE task_id = ? ORDER BY changed_at");
      const auditRecords = [];
      while (auditStmt.step()) {
        auditRecords.push(auditStmt.getAsObject());
      }
      
      if (auditRecords.length > 0) {
        console.log(`✅ 找到 ${auditRecords.length} 条审计记录:`);
        auditRecords.forEach(record => {
          console.log(`  - ${record.change_type}: ${record.old_is_issued} -> ${record.new_is_issued} at ${record.changed_at}`);
        });
      } else {
        console.log('❌ 未找到审计记录');
      }
      
      // 回滚事务以避免污染数据
      db.run("ROLLBACK");
      console.log('✅ 事务已回滚');
    } catch (error) {
      console.error('❌ 审计功能测试失败:', error.message);
      // 尝试回滚事务
      try {
        db.run("ROLLBACK");
      } catch (rollbackError) {
        console.error('❌ 事务回滚失败:', rollbackError.message);
      }
    }
    
    db.close();
    console.log('\n✅ 审计设置验证完成');
  } catch (error) {
    console.error('❌ 审计设置验证失败:', error);
  }
}

// 执行验证
verifyAuditSetup();